package com.data.migration.util;

import com.data.migration.entity.Operation;
import com.data.migration.vo.StudentVo;
import com.data.migration.vo.TeacherVo;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.math.BigDecimal;
import java.sql.*;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Peter on 2018/12/7.
 */
public class DBUtil {
    public static int sqlCount = 0;
    public static final Logger logger = LoggerFactory.getLogger(DBUtil.class);
    public static final Logger sqlFileLog = LoggerFactory.getLogger("MigrationSqlLogger");

    // A版数据库配置(测试库)
    public static final String A_Url = "jdbc:mysql://192.168.15.50:3306/waijiaojun4.5.0";
    public static final String A_UserName = "root";
    public static final String A_PassWord = "123456";
    public static Connection A_Connection;

    // 青少版数据库配置(生产环境 正式库）
    public static final String T_Url = "jdbc:mysql://rdssyv919z3879347728.mysql.rds.aliyuncs.com:3306/waijiaojun-teenager";
    public static final String T_UserName = "nativetalk";
    public static final String T_PassWord = "Suerte2015";
    public static Connection T_Connection;

    public static NumberFormat nbf = NumberFormat.getInstance();

    static {
        nbf.setMinimumFractionDigits(6);
    }

    private static Connection getA_Connection() {
        if (A_Connection == null) {
            try {
                logger.info("\n创建数据库连接【A】\n    DBUrl:{}\n    UserName:{}\n    Password:{}\n", A_Url, A_UserName, A_PassWord);
                A_Connection = DriverManager.getConnection(A_Url, A_UserName, A_PassWord);
            } catch (SQLException e) {
                logger.error("\n创建数据库连接失败【A】\n    DBUrl:{}\n    UserName:{}\n    Password:{}\n", A_Url, A_UserName, A_PassWord);
                logger.error(e.getMessage(), e);
            }
        } else {
            logger.info("\n使用现有数据库连接【A】\n    DBUrl:{}\n    UserName:{}\n    Password:{}\n", A_Url, A_UserName, A_PassWord);
        }
        return A_Connection;
    }

    private static Connection getT_Connection() {
        if (T_Connection == null) {
            try {
                logger.info("\n创建数据库连接【T】\n    DBUrl:{}\n    UserName:{}\n    Password:{}\n", T_Url, T_UserName, T_PassWord);
                T_Connection = DriverManager.getConnection(T_Url, T_UserName, T_PassWord);
            } catch (SQLException e) {
                logger.error("\n创建数据库连接失败【T】\n    DBUrl:{}\n    UserName:{}\n    Password:{}\n", T_Url, T_UserName, T_PassWord);
                logger.error(e.getMessage(), e);
            }
        } else {
            logger.info("\n使用现有数据库连接【T】\n    DBUrl:{}\n    UserName:{}\n    Password:{}\n", T_Url, T_UserName, T_PassWord);
        }
        return T_Connection;
    }

    // 获取数据库连接
    public static Connection getConn(String DBType) {
        if ("A".equals(DBType)) {
            return getA_Connection();
        } else if ("T".equals(DBType)) {
            return getT_Connection();
        } else {
            throw new RuntimeException("无效的数据库类型DBType只能是A、T或M，您给的参数DBType是：" + DBType);
        }
    }

    // 批量执行插入语句，每条语句在执行前，先保存
    public static void insertBatch(List<Operation> operationList) throws SQLException {
        long start = System.currentTimeMillis();
        Operation o = operationList.get(0);
        Connection conn = DBUtil.getConn(o.getApp_type());
        for (int i = 0; i < operationList.size(); i++) {
            Operation operation = operationList.get(i);
            // 更新操作
            executeUpdate(conn, operation.getOperation_sql());
        }

        long end = System.currentTimeMillis();
        double duration = (end - start) / 1000;// 持续时长，秒
        logger.info("\n    总耗时：" + duration + "秒");
    }

    // 批量执行sql，每条语句在执行前，先保存
    public static void execSqlBatch(List<Operation> operationList) throws SQLException {
        Operation o = operationList.get(0);
        Connection conn = DBUtil.getConn(o.getApp_type());
        for (int i = 0; i < operationList.size(); i++) {
            Operation operation = operationList.get(i);
            // 更新操作
            if (Operation.operation_type_U.equals(operation.getOperation_type())) {
                executeUpdate(conn, operation.getOperation_sql());
            } else {
                // 查询操作
                executeQuery(conn, operation.getOperation_sql());
            }
        }
    }

    // 执行更新sql
    public static void executeUpdate(Connection conn, String sql) throws SQLException {
        long start = System.nanoTime();
        int count = ++sqlCount;
        Statement statement = conn.createStatement();
        sqlFileLog.info(count + "：" + sql);// 保存sql语句到日志文件
        logger.info(count + "：" + sql);
        int reslut = statement.executeUpdate(sql);
        long end = System.nanoTime();
        double duration = (end - start) / 1000000000.00;// 持续时长，秒
        logger.info("影响行数：" + reslut + "，耗时：" + nbf.format(duration) + "秒");
    }

    // 执行查询
    private static ResultSet executeQuery(Connection conn, String sql) throws SQLException {
        long start = System.nanoTime();
        int count = ++sqlCount;
        Statement statement = conn.createStatement();
        sqlFileLog.info(count + "：" + sql);// 保存sql语句到日志文件
        logger.info(count + "：" + sql);
        ResultSet rs = statement.executeQuery(sql);
        long end = System.nanoTime();
        double duration = (end - start) / 1000000000.00;// 持续时长，秒
        logger.info("耗时：" + nbf.format(duration) + "秒");
        return rs;
    }

    public static List<Operation> buildInsertSql(Connection conn, String tableName, String pkName, String step, String stepDesc) throws SQLException {
        return buildInsertSql(conn,tableName,pkName,null,step,stepDesc);
    }

    public static List<Operation> buildInsertSql(Connection conn, String tableName, String pkName, String where, String step, String stepDesc) throws SQLException {
        List<Operation> operations = new ArrayList<>();//插入课程的sql

        String sql = "select * from " + tableName;
        if (StringUtils.isNotBlank(where)) {
            sql = sql + where;
        }
        ResultSet rs = DBUtil.executeQuery(conn, sql);
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        StringBuilder columns_sql = new StringBuilder("( `app_type`, `t_id`");
        for (int i = 1; i <= columnCount; i++) {
            String columnName = metaData.getColumnName(i);

            // 插入语句中去掉主键列
            if (!pkName.equalsIgnoreCase(columnName)) {
                columns_sql.append(", `" + columnName + "`");
            }
        }
        columns_sql.append(")");
        logger.info(columns_sql.toString());

        while (rs.next()) {
            String insert_sql = "insert into " + tableName + " " + columns_sql + " values ";
            StringBuilder values_sql = new StringBuilder("('T'");
            createValueSql(rs, columnCount, values_sql);
            values_sql.append(");");
            String asql = insert_sql + values_sql;
            logger.info("\n创建sql-->>{}", asql);
            operations.add(new Operation(Operation.app_type_A, Operation.operation_type_U, asql, step, stepDesc));
        }

        return operations;
    }

    private static void createValueSql(ResultSet rs, int columnCount, StringBuilder values_sql) throws SQLException {
        for (int i = 1; i <= columnCount; i++) {
            String value = rs.getString(i);
            if (value == null) {
                values_sql.append(", NULL");
            } else {
                // 处理特殊字符：换行，单引号
                values_sql.append(", '" + value.
                        replaceAll("(\n|\r\n|\r)", "\\\\n")
                        .replaceAll("\\\\", "\\\\\\\\")
                        .replaceAll("'", "\\\\'")
                        + "'");
            }
        }
    }

    // 学生 专用
    public static List<StudentVo> buildInsertSqlForStudent(Connection conn, String tableName, String pkName) throws SQLException {
        List<StudentVo> students = new ArrayList<>();
        String sql = "select * from " + tableName + " ";
        logger.info("sql：", sql);
        ResultSet rs = DBUtil.executeQuery(conn, sql);
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        StringBuilder columns_sql = new StringBuilder("( `t_memb_balance`, `app_type`, `t_id`");
        for (int i = 1; i <= columnCount; i++) {
            String columnName = metaData.getColumnName(i);
            // 插入语句中去掉主键列
            if (!pkName.equalsIgnoreCase(columnName)) {
                columns_sql.append(", `" + columnName + "`");
            }
        }
        columns_sql.append(")");
        logger.info(columns_sql.toString());

        while (rs.next()) {
            String insert_sql = "insert into " + tableName + " " + columns_sql + " values ";
            BigDecimal balacne = rs.getBigDecimal("memb_balance");
            if (balacne == null) {
                balacne = new BigDecimal(0);
            }
            StringBuilder values_sql = new StringBuilder("('" + balacne + "','T'");
            createValueSql(rs, columnCount, values_sql);
            values_sql.append(");");
            String asql = insert_sql + values_sql;// 插入语句

            // 更新语句
            Integer memb_id = rs.getInt("memb_id");
            String memb_phone = rs.getString("memb_phone");
            BigDecimal memb_balance = rs.getBigDecimal("memb_balance");
            String updateSql = "update td_nt_member set MEMB_BALANCE = memb_balance + " + memb_balance + ", t_id=" + memb_id + ", t_memb_balance=" + memb_balance + "  where memb_phone='" + memb_phone + "' ";
            StudentVo student = new StudentVo(memb_id, memb_phone, memb_balance, asql, updateSql);

            logger.info("\n创建student Insert Sql-->>{}", asql);
            logger.info("\n创建student Update Sql-->>{}", updateSql);
            students.add(student);
        }

        return students;
    }

    public static void migrationForStudent(List<StudentVo> students) throws SQLException {
        Connection conn = getConn("A");

        for (StudentVo student : students) {
            // 查询A版是否存在该学生账号
            String seachSql = "select * from td_nt_member where MEMB_PHONE='" + student.getMemb_phone() + "'";
            PreparedStatement pstat = conn.prepareStatement(seachSql);
            ResultSet rs = pstat.executeQuery();
            boolean haveAccount = rs.next();
            // 该账号在A版中已经存在
            if (haveAccount) {
                executeUpdate(conn, student.getUpdateSql());
            }
            // 账号不存在
            else {
                executeUpdate(conn, student.getInsertSql());
            }
        }
    }

    public static void main(String[] args) throws SQLException {
        Connection conn = getConn(Operation.app_type_T);
        String sql = "select * from td_nt_member";
        executeQuery(conn, sql);
    }

    // 老师 专用
    public static List<TeacherVo> buildInsertSqlForTeacher(Connection conn, String tableName, String pkName) throws SQLException {
        List<TeacherVo> teachers = new ArrayList<>();
        String sql = "select * from " + tableName + "";
        logger.info("sql：", sql);
        ResultSet rs = DBUtil.executeQuery(conn, sql);
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        StringBuilder columns_sql = new StringBuilder("( `t_tea_balance`,`app_type`, `t_id`");
        for (int i = 1; i <= columnCount; i++) {
            String columnName = metaData.getColumnName(i);
            // 插入语句中去掉主键列
            if (!pkName.equalsIgnoreCase(columnName)) {
                columns_sql.append(", `" + columnName + "`");
            }
        }
        columns_sql.append(")");
        logger.info(columns_sql.toString());

        while (rs.next()) {
            String insert_sql = "insert into " + tableName + " " + columns_sql + " values ";
            BigDecimal balacne = rs.getBigDecimal("tea_balance");
            if (balacne == null) {
                balacne = new BigDecimal(0);
            }
            StringBuilder values_sql = new StringBuilder("('" + balacne + "','T'");

            createValueSql(rs, columnCount, values_sql);
            values_sql.append(");");
            String asql = insert_sql + values_sql;// 插入语句

            // 更新语句
            Integer tea_id = rs.getInt("tea_id");
            String tea_email = rs.getString("tea_email");
            BigDecimal tea_balance = rs.getBigDecimal("tea_balance");
            String updateSql = "update td_nt_teacher set tea_balance = tea_balance + " + tea_balance + ", t_id=" + tea_id + ", t_tea_balance=" + tea_balance + "  where tea_email='" + tea_email + "' ";
            TeacherVo teacher = new TeacherVo(tea_id, tea_email, tea_balance, asql, updateSql);

            logger.info("\n创建student Insert Sql-->>{}", asql);
            logger.info("\n创建student Update Sql-->>{}", updateSql);
            teachers.add(teacher);
        }
        return teachers;
    }

    public static void migrationForTeacher(List<TeacherVo> teachers) throws SQLException {
        Connection conn = getConn("A");

        for (TeacherVo teacher : teachers) {
            // 查询A版是否存在该学生账号
            String seachSql = "select * from td_nt_teacher where TEA_EMAIL='" + teacher.getTea_email() + "'";
            PreparedStatement pstat = conn.prepareStatement(seachSql);
            ResultSet rs = pstat.executeQuery();
            boolean haveAccount = rs.next();
            // 该账号在A版中已经存在
            if (haveAccount) {
                executeUpdate(conn, teacher.getUpdateSql());
            }
            // 账号不存在
            else {
                executeUpdate(conn, teacher.getInsertSql());
            }
        }
    }


    // 创建更新外键的sql语句
    public static String createUpdateFkSql(String ftableName, String fk, String ptableName, String pk) {
        String sql = "UPDATE " + ftableName + " as A\n" +
                "INNER JOIN (select " + pk + ",t_id,app_type from " + ptableName + " where t_id is not null) as B\n" +
                "ON A." + fk + " = B.t_id\n" +
                "SET A." + fk + "=B." + pk + "\n" +
                "where A.app_type='T';";
        return sql;
    }
}
